package com.dmall.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

import com.dmall.pagedata.PageData;

public class DBUtil {
	
	public DBUtil() {
	}
	/**
	 * 建立连接，返回一个连接对象
	 * @return
	 */
	public static Connection getConn() {
		String url = "jdbc:mysql://192.168.12.23:3308/dmall?serverTimezone=Asia/Shanghai";
		String user = "root";
		String pwd = "root";
		Connection conn = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * 释放资源
	 */
	public static void closeResource(Connection con, PreparedStatement ps, ResultSet rs) {

		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 增删改的通用代码优化
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static boolean exeUpdate(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int n = 0;
		try {
			//获取数据库连接对象
			conn =  getConn();
			//预编译sql语句，返回PreparedStatement实例
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
				//执行sql
				n = ps.executeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//释放资源
			closeResource(conn, ps, rs);
		}
		return n > 0;
	}

	/**
	 * 通用查询方法(返回结果是一个集合)
	 * 
	 * @param sql    查询语句
	 * @param cla    Class对象
	 * @param params 可变参数
	 * @return List 集合
	 */
	public static List exeQuery(String sql, Class clac, Object... params) {
		List list = new ArrayList();
		Connection conn = getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object obj = null;
		try {
			if (params != null) {
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
				rs = ps.executeQuery();
			}
			while (rs.next()) {
				obj = conver(rs, clac);
				list.add(obj);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeResource(conn, ps, rs);
		}
		return list;
	}
	
	/**
	 *	分页查询
	 * @param sql
	 * @param clac
	 * @param pageNo 页码
	 * @param pageSize 当前页的记录数
	 * @param params
	 * @return
	 */
	public static PageData exeQueryByPage(String sql, Class clac,int pageNo,int pageSize, Object... params) {
		List list = new ArrayList();
		// 没有拼接limit之前，先查询sql，目的只是得到"总记录数"	
		String totalCoutsql = "select count(*) as c from ( "+sql+" ) as t";
		int totalCout = exeCountQuery(totalCoutsql, params);
		//起始位置
		int start = (pageNo-1)*pageSize;
		//分页查询sql
		sql = sql + " limit " + start+","+pageSize;
		Connection conn = getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object obj = null;
		try {
			if (params != null) {
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
				rs = ps.executeQuery();
			}
			while (rs.next()) {
				obj = conver(rs, clac);
				list.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeResource(conn, ps, rs);
		}
		PageData pd = new PageData<Object>();
		pd.setPageNo(pageNo);
		pd.setPageSize(pageSize);
		pd.setList(list);
		pd.setTotalCount(totalCout);
		return pd;
	}
	/**
	 *	查询单个记录（总记录数）
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int exeCountQuery(String sql, Object... params) {
		int totalCount = 0;
		Connection conn = getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			if (params != null) {
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
				rs = ps.executeQuery();
			}
			while (rs.next()) {
				totalCount = rs.getInt("c");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeResource(conn, ps, rs);
		}
		return totalCount;
	}
	
	
	/**
	 * 转换方法 将rs读取结果转换为对象
	 * 
	 * @param rs
	 * @param cla
	 * @return
	 */
	public static Object conver(ResultSet rs, Class calc) {//Class calc = xx.class.newInstance
		
		Object obj = null;
		try {
			// 获取到一个对象，并赋值给obj
			obj = calc.newInstance();
			// getMetaData()获取字段对象，包括了数据的字段名称、类型以及数目等表格所必须具备的信息。
			ResultSetMetaData rsmd = rs.getMetaData();
			// getColumnCount()用来获取数据库表中的列数
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				// 获取数据库表度字段名
				String name = rsmd.getColumnLabel(i);
				// 获取字段相应的值
				Object objvalue = rs.getObject(name);
				BeanUtils.setProperty(obj, name, objvalue);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;

	}

}